﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;
using System.Collections;
namespace DAL
{
    public class StudentsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoStudents model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Students]");
            sql.Append("([STU_ID],[STU_CODE],[STU_NAME],[STU_USERNAME],[STU_PASSWORD],[STU_GENDER],[STU_MAJOR],[STU_CLASSES],[STU_GRADE],[STU_QUESTION],[STU_IDNUMBER],[STU_ANSWER],[STU_TEL],[STU_EMAIL],[STU_IsShow])");
            sql.Append("VALUES");
            sql.Append("(@STU_ID,@STU_CODE,@STU_NAME,@STU_USERNAME,@STU_PASSWORD,@STU_GENDER,@STU_MAJOR,@STU_CLASSES,@STU_GRADE,@STU_QUESTION,@STU_IDNUMBER,@STU_ANSWER,@STU_TEL,@STU_EMAIL,@STU_IsShow)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoStudents model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Students] SET ");
            sql.Append("STU_ID=@STU_ID,");
            sql.Append("STU_CODE=@STU_CODE");
            sql.Append(",STU_NAME=@STU_NAME");
            sql.Append(",STU_USERNAME=@STU_USERNAME");
            sql.Append(",STU_PASSWORD=@STU_PASSWORD");
            sql.Append(",STU_GENDER=@STU_GENDER");
            sql.Append(",STU_MAJOR=@STU_MAJOR");
            sql.Append(",STU_CLASSES=@STU_CLASSES");
            sql.Append(",STU_GRADE=@STU_GRADE,");
            sql.Append("STU_QUESTION=@STU_QUESTION");
            sql.Append(",STU_ANSWER=@STU_ANSWER");
            sql.Append(",STU_IDNUMBER=@STU_IDNUMBER");
            sql.Append(",STU_EMAIL=@STU_EMAIL");
            sql.Append(",STU_TEL=@STU_TEL");
            sql.Append(",STU_IsShow=@STU_IsShow");
            sql.Append(" WHERE STU_ID=@STU_ID");
          

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 根据班级id来删除学生
        /// </summary>
        /// <param name="classId"></param>
        public void DeleteByClssId(string classId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Students");
            sql.Append(" WHERE ");
            sql.Append("STU_CLASSES=@classId");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@classId", SqlDbType.NVarChar, 50);
            parameter[0].Value = classId;
            dbhelper.ExcuteNoQuery(sql.ToString(), parameter);        
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string sTU_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Students] SET");
            sql.Append(" STU_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("STU_ID=@STU_ID ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@STU_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = sTU_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 假批量删除一批学生
        /// </summary>
        /// <param name="StudentsId">学生id的数组</param>
        public int FDleteMoreStudents(ArrayList StudentsIdArray)
        {
            if (StudentsIdArray.Count > 0)
            {
                StringBuilder studentsid = new StringBuilder();
                for (int i = 0; i < StudentsIdArray.Count - 1; i++)
                {
                    studentsid.Append("'");
                    studentsid.Append(StudentsIdArray[i]);
                    studentsid.Append("',");
                }
                studentsid.Append("'");
                studentsid.Append(StudentsIdArray[StudentsIdArray.Count - 1]);
                studentsid.Append("'");
                StringBuilder sql = new StringBuilder();
                sql.Append("UPDATE [Students]  SET  STU_IsShow ='false' WHERE STU_ID in (" + studentsid + ")");
                return dbhelper.ExcuteNoQuery(sql.ToString());
            }
            else
                return 0;
        }
         /// <summary>
        /// 批量删除一批学生
        /// </summary>
        /// <param name="StudentsId">学生id的数组</param>
        public int DleteMoreStudents(ArrayList StudentsIdArray)
        {

            if (StudentsIdArray.Count > 0)
            {
                StringBuilder studentsid = new StringBuilder();
                for (int i = 0; i < StudentsIdArray.Count - 1; i++)
                {
                    studentsid.Append("'");
                    studentsid.Append(StudentsIdArray[i]);
                    studentsid.Append("',");
                }
                studentsid.Append("'");
                studentsid.Append(StudentsIdArray[StudentsIdArray.Count - 1]);
                studentsid.Append("'");
                StringBuilder sql = new StringBuilder();
                sql.Append("DELETE FROM [Students] WHERE STU_ID in (" + studentsid + ")");
                return dbhelper.ExcuteNoQuery(sql.ToString());
            }
            else
                return 0;
        }
         /// <summary>
        /// 根据学生的对象来核对是否有此学生
        /// </summary>
        /// <param name="student">学生对象</param>
        /// <returns>返回bool值</returns>
        public BoStudents CheckStudent(BoStudents model)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from [Students] where STU_USERNAME =@STU_USERNAME and STU_PASSWORD = @STU_PASSWORD and STU_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), GetParameters(model));
            IList<BoStudents> list = GetList(table);
            return list!=null&&list.Count > 0 ? list[0] : null;
        }


        /// <summary>
        /// 根据多个班级id来删除删除一批学生
        /// </summary>
        /// <param name="ClassIdArray">班级id的数组</param>
        public void DleteMoreByClassArr(ArrayList classesArray)
        {

            if (classesArray.Count > 0)
            {
                StringBuilder classesid = new StringBuilder();
                for (int i = 0; i < classesArray.Count - 1; i++)
                {
                    classesid.Append("'");
                    classesid.Append(classesArray[i]);
                    classesid.Append("',");
                }
                classesid.Append("'");
                classesid.Append(classesArray[classesArray.Count - 1]);
                classesid.Append("'");
                StringBuilder sql = new StringBuilder();
                sql.Append("DELETE FROM [Students] WHERE STU_CLASSES in (" + classesid + ")");
                dbhelper.ExcuteNoQuery(sql.ToString());
            }
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoStudents GetModel(string sTU_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT STU_ID,STU_CODE,STU_NAME,STU_USERNAME,STU_PASSWORD,STU_GENDER,STU_CLASSES,STU_GRADE,STU_MAJOR,STU_IsShow,STU_QUESTION,STU_ANSWER,STU_TEL,STU_EMAIL,STU_IDNUMBER FROM Students");
            sql.Append(" WHERE ");
            sql.Append("STU_ID=@STU_ID AND STU_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@STU_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = sTU_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }


        /// <summary>
        /// 根据学号来获取一条记录
        /// </summary>
        /// <param name="STU_Code">学号</param>
        /// <returns></returns>
        public BoStudents GetModelByCode(string STU_Code)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT STU_ID,STU_CODE,STU_NAME,STU_USERNAME,STU_PASSWORD,STU_GENDER,STU_CLASSES,STU_GRADE,STU_MAJOR,STU_IsShow,STU_QUESTION,STU_ANSWER,STU_TEL,STU_EMAIL,STU_IDNUMBER FROM Students");
            sql.Append(" WHERE ");
            sql.Append("STU_CODE=@STU_Code AND STU_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@STU_Code", SqlDbType.NVarChar, 50);
            parameter[0].Value = STU_Code;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }


        /// <summary>
        /// 根据用户名来获取一条记录
        /// </summary>
        /// <param name="STU_Code">学号</param>
        /// <returns></returns>
        public BoStudents GetModelByUserName(string STU_Name)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT STU_ID,STU_CODE,STU_NAME,STU_USERNAME,STU_PASSWORD,STU_GENDER,STU_CLASSES,STU_GRADE,STU_MAJOR,STU_IsShow,STU_QUESTION,STU_ANSWER,STU_TEL,STU_EMAIL,STU_IDNUMBER FROM Students");
            sql.Append(" WHERE ");
            sql.Append("STU_USERNAME=@STU_USERNAME AND STU_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@STU_USERNAME", SqlDbType.NVarChar, 50);
            parameter[0].Value = STU_Name;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
        /// <summary>
        /// 获取所有符合条件的学生数据
        /// </summary>
        /// <param name="sqlcon">sql条件</param>
        /// <returns>返回相应的数据集</returns>
        public List<BoStudents> GetAllStudents(object sqlcon)
        {
            StringBuilder sql = new StringBuilder();
            DataTable table = new DataTable();
            sql.Append("SELECT * FROM Students WHERE 1=1 and STU_IsShow='true'");
            if (sqlcon != null && !string.IsNullOrEmpty(sqlcon.ToString()))
                sql.Append(sqlcon);
            table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoStudents> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

        public IList<BoStudents> GetStudentsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT STU_ID,STU_CODE,STU_NAME,STU_USERNAME,STU_PASSWORD,STU_GENDER,STU_CLASSES,STU_GRADE,STU_MAJOR,STU_IsShow,STU_QUESTION,STU_ANSWER,STU_TEL,STU_EMAIL,STU_IDNUMBER ");
            sql.Append("FROM Students AS S INNER JOIN Classes AS C ON S.STU_CLASSES=C.CLA_ID ");
            sql.Append("WHERE C.CLA_ACADEMY=@ACA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ACA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list;
        }
        /// <summary>
        /// 根据学生id来从学生视图中获取单条数据
        /// </summary>
        /// <param name="stundetId"></param>
        /// <returns></returns>
        public BoStudents GetV_Student(string stundetId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * from v_Students where STU_ID = @STU_ID and STU_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1]{
                new SqlParameter("@STU_ID",SqlDbType.NVarChar)
            };
            parameter[0].Value = stundetId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetVList(table);
            return list!=null&&list.Count>0?list[0]:null;
        }
        public IList<BoStudents> GetStudentsByClass(string classId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT STU_ID,STU_CODE,STU_NAME,STU_USERNAME,STU_PASSWORD,STU_GENDER,STU_CLASSES,STU_GRADE,STU_MAJOR,STU_IsShow,STU_QUESTION,STU_ANSWER,STU_TEL,STU_EMAIL,STU_IDNUMBER ");
            sql.Append("FROM Students ");
            sql.Append("WHERE STU_CLASSES=@CLA_ID AND STU_IsShow='true'");

            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CLA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = classId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list;
        }
        public IList<BoStudents> GetStudentsByGroup(string groupId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM Students AS S LEFT JOIN Participants AS P ON S.STU_ID=P.PAR_STUDENTS ");
            sql.Append(" WHERE ");
            sql.Append(" P.PAR_GROUPS=@PAR_GROUPS AND STU_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PAR_GROUPS", SqlDbType.NVarChar, 50);
            parameter[0].Value = groupId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            List<BoStudents> list = GetList(table);
            return list;
        }
        /// <summary>
        /// 根据表格获得视图列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private List<BoStudents> GetVList(DataTable table)
        {
            List<BoStudents> list = new List<BoStudents>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoStudents model = new BoStudents();
                    model.STU_ID = table.Rows[i]["STU_ID"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_ID"];
                    model.STU_CODE = table.Rows[i]["STU_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_CODE"];
                    model.STU_NAME = table.Rows[i]["STU_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_NAME"];
                    model.STU_USERNAME = table.Rows[i]["STU_USERNAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_USERNAME"];
                    model.STU_PASSWORD = table.Rows[i]["STU_PASSWORD"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_PASSWORD"];
                    model.STU_GENDER = table.Rows[i]["STU_GENDER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_GENDER"];
                    model.STU_MAJOR = table.Rows[i]["STU_MAJOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_MAJOR"];
                    model.STU_CLASSES = table.Rows[i]["STU_CLASSES"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_CLASSES"];
                    model.STU_GRADE = table.Rows[i]["STU_GRADE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_GRADE"];
                    model.STU_IDNUMBER = table.Rows[i]["STU_IDNUMBER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_IDNUMBER"];
                    model.STU_QUESTION = table.Rows[i]["STU_QUESTION"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_QUESTION"];
                    model.STU_ANSWER = table.Rows[i]["STU_ANSWER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_ANSWER"];
                    model.STU_TEL = table.Rows[i]["STU_TEL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_TEL"];
                    model.STU_EMAIL = table.Rows[i]["STU_EMAIL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_EMAIL"];
                    model.STU_IsShow = table.Rows[i]["STU_IsShow"] == System.DBNull.Value ? false : (bool)table.Rows[i]["STU_IsShow"];

                    model.classes.CLA_CODE = (string)table.Rows[i]["CLA_CODE"];
                    model.classes.CLA_NAME = (string)table.Rows[i]["CLA_NAME"];
                    model.classes.CLA_ACADEMY = (string)table.Rows[i]["CLA_ACADEMY"];

                    model.academy.ACA_ID = (string)table.Rows[i]["ACA_ID"];
                    model.academy.ACA_NAME = (string)table.Rows[i]["ACA_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据表格获得普通列表
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private List<BoStudents> GetList(DataTable table)
        {
            List<BoStudents> list = new List<BoStudents>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoStudents model = new BoStudents();
                    model.STU_ID = table.Rows[i]["STU_ID"]==System.DBNull.Value?"":(string)table.Rows[i]["STU_ID"];
                    model.STU_CODE = table.Rows[i]["STU_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_CODE"];
                    model.STU_NAME = table.Rows[i]["STU_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_NAME"];
                    model.STU_USERNAME = table.Rows[i]["STU_USERNAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_USERNAME"];
                    model.STU_PASSWORD = table.Rows[i]["STU_PASSWORD"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_PASSWORD"];
                    model.STU_GENDER = table.Rows[i]["STU_GENDER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_GENDER"];
                    model.STU_MAJOR = table.Rows[i]["STU_MAJOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_MAJOR"];
                    model.STU_CLASSES = table.Rows[i]["STU_CLASSES"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_CLASSES"];
                    model.STU_GRADE = table.Rows[i]["STU_GRADE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_GRADE"];
                    model.STU_IDNUMBER = table.Rows[i]["STU_IDNUMBER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_IDNUMBER"];
                    model.STU_QUESTION = table.Rows[i]["STU_QUESTION"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_QUESTION"];
                    model.STU_ANSWER = table.Rows[i]["STU_ANSWER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_ANSWER"];
                    model.STU_TEL = table.Rows[i]["STU_TEL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_TEL"];
                    model.STU_EMAIL = table.Rows[i]["STU_EMAIL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["STU_EMAIL"];
                    model.STU_IsShow = table.Rows[i]["STU_IsShow"] == System.DBNull.Value ? false : (bool)table.Rows[i]["STU_IsShow"];
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoStudents model)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@STU_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_USERNAME",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_PASSWORD",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_GENDER",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_MAJOR",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_CLASSES",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_GRADE",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_IDNUMBER",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_QUESTION",SqlDbType.NVarChar,100),
                new SqlParameter("@STU_ANSWER",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_TEL",SqlDbType.NVarChar,50),
                new SqlParameter("@STU_EMAIL",SqlDbType.NVarChar,100),
                new SqlParameter("@STU_IsShow",SqlDbType.Bit,2),
            };
            parameters[0].Value = model.STU_ID;
            parameters[1].Value = model.STU_CODE;
            parameters[2].Value = model.STU_NAME;
            parameters[3].Value = model.STU_USERNAME;
            parameters[4].Value = model.STU_PASSWORD;
            parameters[5].Value = model.STU_GENDER;
            parameters[6].Value =model.STU_MAJOR;
            parameters[7].Value = model.STU_CLASSES;
            parameters[8].Value = model.STU_GRADE;
            parameters[9].Value = model.STU_IDNUMBER;
            parameters[10].Value = model.STU_QUESTION;
            parameters[11].Value = model.STU_ANSWER;
            parameters[12].Value = model.STU_TEL;
            parameters[13].Value = model.STU_EMAIL;
            parameters[14].Value = model.STU_IsShow;
            return parameters;
        }

        /// <summary>
        /// 根据条件来获得视图v_StudentToContest表格
        /// </summary>
        /// <param name="constr">条件</param>
        /// <returns>返回表格</returns>
        public DataTable Getv_StudentToContest(string constr)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from v_StudentToContest where 1=1 ");
            if (!string.IsNullOrEmpty(constr))
            {
                sql.Append("and ");
                sql.Append(constr);
            }
            return dbhelper.ExcuteQuery(sql.ToString());
        }
         /// <summary>
        /// 通过学生的学号来获得相应的数据
        /// </summary>
        /// <param name="studnentCode">学号</param>
        /// <returns>返回学生对象</returns>
        public BoStudents GetV_studentByCode(string studentCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * from v_Students where STU_CODE = @STU_CODE");
            SqlParameter[] parameter = new SqlParameter[1]{
                new SqlParameter("@STU_CODE",SqlDbType.NVarChar)
            };
            parameter[0].Value = studentCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetVList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        /// <summary>
        /// 通过学号来获取相应的学生对象
        /// </summary>
        /// <param name="studnetCode">学号</param>
        /// <returns>返回学生对象</returns>
        public BoStudents getStudentByCode(string studnetCode)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * from Students where STU_CODE = @STU_CODE");
            SqlParameter[] parameter = new SqlParameter[1]{
                new SqlParameter("@STU_CODE",SqlDbType.NVarChar)
            };
            parameter[0].Value = studnetCode;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoStudents> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
    }
}